//这个函数在整个wps加载项中是第一个执行的
function OnAddinLoad(ribbonUI) {
    if (typeof (wps.ribbonUI) != "object") {
        wps.ribbonUI = ribbonUI
    }

    if (typeof (wps.Enum) != "object") { // 如果没有内置枚举值
        wps.Enum = WPS_Enum
    }

    wps.PluginStorage.setItem("EnableFlag", false) //往PluginStorage中设置一个标记，用于控制两个按钮的置灰
    wps.PluginStorage.setItem("ApiEventFlag", false) //往PluginStorage中设置一个标记，用于控制ApiEvent的按钮label
    return true
}

var WebNotifycount = 0;
function OnAction(control) {
    const eleId = control.Id;
    switch (eleId) {
        case "btnShowMsg": {
            const doc = wps.EtApplication().ActiveWorkbook;
            if (!doc) {
                alert("当前没有打开任何文档");
                return;
            }
            alert(doc.Name);

            let s = wps.Application.Selection;

            alert("Row:" + s.Row + ", Rows Count:" + s.Rows.Count + "\r\n" +
                "Column:" + s.Column + ", Columns Count:" + s.Columns.Count)
            break;
        }
        case "btnIsEnbable": {
            let bFlag = wps.PluginStorage.getItem("EnableFlag")
            wps.PluginStorage.setItem("EnableFlag", !bFlag)

            //通知wps刷新以下几个按饰的状态
            wps.ribbonUI.InvalidateControl("btnIsEnbable")
            wps.ribbonUI.InvalidateControl("btnShowDialog")
            wps.ribbonUI.InvalidateControl("btnShowTaskPane")
            wps.ribbonUI.InvalidateControl("btnShowFindPane")
            //wps.ribbonUI.Invalidate(); 这行代码打开则是刷新所有的按钮状态
            break;
        }
        case "btnShowDialog": {
            wps.ShowDialog(GetUrlPath() + "/ui/dialog.html", "这是一个对话框网页", 400 * window.devicePixelRatio, 400 * window.devicePixelRatio, false);
            break;
        }
        case "btnShowTaskPane": {
            let tsId = wps.PluginStorage.getItem("taskpane_id")
            if (!tsId) {
                let tskpane = wps.CreateTaskPane(GetUrlPath() + "/ui/taskpane.html")
                let id = tskpane.ID
                wps.PluginStorage.setItem("taskpane_id", id)
                tskpane.Visible = true
            } else {
                let tskpane = wps.GetTaskPane(tsId)
                tskpane.Visible = !tskpane.Visible
            }
            break;
        }
        case "btnApiEvent": {
            let bFlag = wps.PluginStorage.getItem("ApiEventFlag");
            let bRegister = bFlag ? false : true;
            wps.PluginStorage.setItem("ApiEventFlag", bRegister);
            if (bRegister) {
                wps.ApiEvent.AddApiEventListener('NewWorkbook', OnNewDocumentApiEvent);
            }
            else {
                wps.ApiEvent.RemoveApiEventListener('NewWorkbook', OnNewDocumentApiEvent);
            }

            wps.ribbonUI.InvalidateControl("btnApiEvent");
            break;
        }
        case "btnWebNotify": {
            let currentTime = new Date();
            let timeStr = currentTime.getHours() + ':' + currentTime.getMinutes() + ":" + currentTime.getSeconds();
            wps.OAAssist.WebNotify("这行内容由wps加载项主动送达给业务系统，可以任意自定义, 比如时间值:" + timeStr + "，次数：" + (++WebNotifycount), true);
            break;
        }
        case "btnShowFindPane": {
            let findpaneId = wps.PluginStorage.getItem("findpane_id");
            if (!findpaneId) {
                let findpane = wps.CreateTaskPane(GetUrlPath() + "/ui/findpane.html", "按列查找");
                let id = findpane.ID;
                wps.PluginStorage.setItem("findpane_id", id);
                findpane.Visible = true;
            } else {
                let findpane = wps.GetTaskPane(findpaneId);
                findpane.Visible = !findpane.Visible;
            }
            break;
        }
        case "btnHookData":{
            let hookpaneId = wps.PluginStorage.getItem("hookpane_id");
            if (!hookpaneId) {
                let hookpane = wps.CreateTaskPane(GetUrlPath() + "/ui/hookpane.html", "钩取数据");
                let id = hookpane.ID;
                wps.PluginStorage.setItem("hookpane_id", id);
                hookpane.Visible = true;
            } else {
                let hookpane = wps.GetTaskPane(hookpaneId);
                hookpane.Visible = !hookpane.Visible;
            }
            break;
        }
        case "btnMultiReplace":{
            let multireplacepaneId = wps.PluginStorage.getItem("multireplacepane_id");
            if (!multireplacepaneId) {
                let multireplacepane = wps.CreateTaskPane(GetUrlPath() + "/ui/multireplacepane.html", "复合替换");
                let id = multireplacepane.ID;
                wps.PluginStorage.setItem("multireplacepane_id", id);
                multireplacepane.Visible = true;
            } else {
                let multireplacepane = wps.GetTaskPane(multireplacepaneId);
                multireplacepane.Visible = !multireplacepane.Visible;
            }
            break;
        }
        case "btnDropDataByFields": {
            let dropdataPaneId = wps.PluginStorage.getItem("dropdatapane_id");
            if (!dropdataPaneId) {
                let dropdatapane = wps.CreateTaskPane(GetUrlPath() + "/ui/dropcolspane.html", "按字段提取数据");
                let id = dropdatapane.ID;
                wps.PluginStorage.setItem("dropdatapane_id", id);
                dropdatapane.Visible = true;
            } else {
                let dropdatapane = wps.GetTaskPane(dropdataPaneId);
                dropdatapane.Visible = !dropdatapane.Visible;
            }
            break;
        }
        case "btnValidateBankNumber": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            if(s.Areas.Count > 1){
                alert('此功能用于多重选择区时会出错！');
                return;
            }

            let wrongsCount = 0;
            let rightsCount = 0;
            for (i = 1; i <= s.Count; i++) {
                let rng = s.Item(i);
                if (rng == undefined) {
                    wrongsCount += 1;
                    continue;
                }
                if (rng.Value2 == undefined) {
                    rng.Interior.Pattern = 1;  //xlPatternSolid;
                    rng.Interior.ColorIndex = 39;
                    rng.Interior.TintAndShade = 0;
                    rng.Interior.PatternColorIndex = -4105;
                    wrongsCount += 1;
                    continue;
                }
                if (luhmCheck(rng.Value2.toString()) == false) {
                    rng.Interior.Pattern = 1;  //xlPatternSolid;
                    rng.Interior.Color = 65535;
                    rng.Interior.TintAndShade = 0;
                    rng.Interior.PatternColorIndex = -4105;
                    wrongsCount += 1;
                } else {
                    rng.Interior.Pattern = -4142; //xlPatternNone;
                    rightsCount += 1;
                }
            }

            if (s.Count == rightsCount) {
                alert(`${rightsCount}个单元格中的银行卡号全部验证通过。`)
            } else {
                alert(`已验证${s.Count}个单元格中的银行卡号，其中验证通过${rightsCount}个单元格，验证失败${wrongsCount}个单元格（以黄色背景标示），空值为浅紫色。`);
            }
            break;
        }
        case "btnValidateIdNumber": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            if(s.Areas.Count > 1){
                alert('此功能用于多重选择区时会出错！');
                return;
            }

            let wrongsCount = 0;
            let rightsCount = 0;
            let emptyCount = 0;
            for (i = 1; i <= s.Count; i++) {
                let rng = s.Item(i);
                if (rng == undefined) {
                    continue;
                    wrongsCount += 1;
                }
                if (rng.Value2 == undefined || rng.Value2.toString() == "") {
                    rng.Interior.Pattern = 1;  //xlPatternSolid;
                    rng.Interior.ColorIndex = 38;
                    rng.Interior.TintAndShade = 0;
                    rng.Interior.PatternColorIndex = -4105;
                    wrongsCount += 1;
                    emptyCount += 1;
                    continue;
                }
                if (IdentityCodeValid(rng.Value2.toString()) == false) {
                    rng.Interior.Pattern = 1;  //xlPatternSolid;
                    rng.Interior.Color = 65535;
                    rng.Interior.TintAndShade = 0;
                    rng.Interior.PatternColorIndex = -4105;
                    wrongsCount += 1;
                } else {
                    rng.Interior.Pattern = -4142; //xlPatternNone;
                    rightsCount += 1;
                }
            }
            if (s.Count == rightsCount) {
                alert(`${rightsCount}个单元格中的身份证号全部验证通过。`)
            } else {
                alert(`已验证${s.Count}个单元格中的身份证号，其中验证通过${rightsCount}个单元格，验证失败${wrongsCount}个单元格（以黄色背景标示），其中空单元格${emptyCount}个（以水红色背景标示）。`);
            }
            break;
        }
        case "btnSetBackgroundByField": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            if (s.Columns.Count != 1) {
                alert("此功能只支持单列！");
                return false;
            }

            let preValue = undefined;
            let setPreBack = false;
            for (i = 1; i <= s.Count; i++) {
                let cell = s.Item(i);
                if (cell == undefined) {
                    continue;
                }

                if (cell.Value2 != preValue) {
                    setPreBack = !setPreBack;
                }

                preValue = cell.Value2;

                let startCol = aSheet.UsedRange.Column;
                let endCol = aSheet.UsedRange.Column + aSheet.UsedRange.Columns.Count - 1;

                if (s.Column < startCol || s.Column > endCol) {
                    alert("请在当前有数据的区域选择一个字段！");
                    return false;
                }

                // let row = aSheet.Rows.Item(cell.Row + ":" + cell.Row);
                let startCell = aSheet.Cells.Item(cell.Row, startCol);
                let endCell = aSheet.Cells.Item(cell.Row, endCol);
                let rng = aSheet.Range(startCell, endCell);
                if (setPreBack) {
                    rng.Interior.Pattern = 1;  //xlPatternSolid;
                    rng.Interior.ThemeColor = 8;
                    rng.Interior.TintAndShade = 0.8;
                    continue;
                } else {
                    rng.Interior.Pattern = -4142; //xlPatternNone;
                }
            }
            break;
        }
        case "btnSetBackgroundByFstchar": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            if (s.Columns.Count != 1) {
                alert("此功能只支持单列！");
                return false;
            }

            let preValue = undefined;
            let setPreBack = false;
            for (i = 1; i <= s.Count; i++) {
                let cell = s.Item(i);
                if (cell == undefined) {
                    continue;
                }

                var txtVal = String(cell.Value2);
                if (txtVal.length > 0) {
                    txtVal = txtVal[0];
                } else {
                    txtVal = cell.Value2;
                }

                if (txtVal != preValue) {
                    setPreBack = !setPreBack;
                }

                preValue = txtVal;

                let startCol = aSheet.UsedRange.Column;
                let endCol = aSheet.UsedRange.Column + aSheet.UsedRange.Columns.Count - 1;

                if (s.Column < startCol || s.Column > endCol) {
                    alert("请在当前有数据的区域选择一个字段列！");
                    return false;
                }

                // let row = aSheet.Rows.Item(cell.Row + ":" + cell.Row);
                let startCell = aSheet.Cells.Item(cell.Row, startCol);
                let endCell = aSheet.Cells.Item(cell.Row, endCol);
                let rng = aSheet.Range(startCell, endCell);
                if (setPreBack) {
                    rng.Interior.Pattern = 1;  //xlPatternSolid;
                    rng.Interior.ThemeColor = 8;
                    rng.Interior.TintAndShade = 0.8;
                    continue;
                } else {
                    rng.Interior.Pattern = -4142; //xlPatternNone;
                }
            }
            break;
        }
        case "btnSetBackgroundByFstcharPinyin": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            if (s.Columns.Count != 1) {
                alert("此功能只支持单列！");
                return false;
            }

            let preValue = undefined;
            let setPreBack = false;
            for (i = 1; i <= s.Count; i++) {
                let cell = s.Item(i);
                if (cell == undefined) {
                    continue;
                }

                var txtVal = String(cell.Value2);
                if (txtVal.length > 0) {
                    // 注意引入外部js 引入第三方库 的方法！
                    // 要在 main.js 中登记第三方 js 的路径。
                    var { pinyin } = pinyinPro;        // 不是用 import 语句，也不是用 require() 方法
                    txtVal = pinyin(txtVal[0])[0];
                } else {
                    txtVal = cell.Value2;
                }

                if (txtVal != preValue) {
                    setPreBack = !setPreBack;
                }

                preValue = txtVal;

                let startCol = aSheet.UsedRange.Column;
                let endCol = aSheet.UsedRange.Column + aSheet.UsedRange.Columns.Count - 1;

                if (s.Column < startCol || s.Column > endCol) {
                    alert("请在当前有数据的区域选择一个字段列！");
                    return false;
                }

                // let row = aSheet.Rows.Item(cell.Row + ":" + cell.Row);
                let startCell = aSheet.Cells.Item(cell.Row, startCol);
                let endCell = aSheet.Cells.Item(cell.Row, endCol);
                let rng = aSheet.Range(startCell, endCell);
                if (setPreBack) {
                    rng.Interior.Pattern = 1;  //xlPatternSolid;
                    rng.Interior.ThemeColor = 8;
                    rng.Interior.TintAndShade = 0.8;
                    continue;
                } else {
                    rng.Interior.Pattern = -4142; //xlPatternNone;
                }
            }
            break;
        }
        case "btnConvertToPinYin": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            wps.EtApplication.ScreenUpdating = false;  // 禁止屏幕刷新
            var { pinyin } = pinyinPro;
            for (let i = 1; i <= s.Cells.Count; i++) {
                let cell = s.Cells.Item(i);
                cell.Value2 = cell.Value2 + ", " + pinyin(cell.Value2, { mode: 'surname' });
            }
            wps.EtApplication.ScreenUpdating = true;  // 恢复屏幕刷新
            break;
        }
        case "btnMail": {
            let mailpane;
            let mailpaneId = wps.PluginStorage.getItem("mailpane_id");
            if (!mailpaneId) {
                mailpane = wps.CreateTaskPane(GetUrlPath() + "/ui/mailpane.html", "标签生成器");
                let id = mailpane.ID;
                wps.PluginStorage.setItem("mailpane_id", id);
                mailpane.Visible = true;
            } else {
                mailpane = wps.GetTaskPane(mailpaneId);
                mailpane.Visible = !mailpane.Visible;
            }
            break;
        }
        case "btnRemoveBlankChars": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            wps.EtApplication.ScreenUpdating = false;  // 禁止屏幕刷新
            for (i = 1; i <= s.Count; i++) {
                let rng = s.Item(i);
                if (rng == undefined) continue;

                if (rng.Value2 != undefined) {
                    let txt = rng.Value2.toString();
                    txt = txt.replace(/\s{1,}/g, '');
                    rng.Value2 = txt;
                }
            }
            wps.EtApplication.ScreenUpdating = true;  // 恢复屏幕刷新
            break;
        }
        case "btnJoinBlankChars": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            wps.EtApplication.ScreenUpdating = false;  // 禁止屏幕刷新
            for (i = 1; i <= s.Count; i++) {
                let rng = s.Item(i);
                if (rng == undefined) continue;

                if (rng.Value2 != undefined) {
                    let txt = rng.Value2.toString();
                    txt = txt.replace(/\s{1,}/g, ' ');
                    rng.Value2 = txt;
                }
            }
            wps.EtApplication.ScreenUpdating = true;  // 恢复屏幕刷新
            break;
        }
        case "btnTrimBlankChars": {
            var aSheet = wps.EtApplication().ActiveSheet;
            let s = wps.Application.Selection;
            wps.EtApplication.ScreenUpdating = false;  // 禁止屏幕刷新
            for (i = 1; i <= s.Count; i++) {
                let rng = s.Item(i);
                if (rng == undefined) continue;

                if (rng.Value2 != undefined) {
                    let txt = rng.Value2.toString();
                    txt = txt.trim();
                    rng.Value2 = txt;
                }
            }
            wps.EtApplication.ScreenUpdating = true;  // 恢复屏幕刷新
            break;
        }
        case "btnGetFileNames": {
            let fileDlg = Application.FileDialog(Application.Enum.msoFileDialogFilePicker);
            fileDlg.InitialFileName = Application.ActiveWorkbook.Path;
            fileDlg.AllowMultiSelect = true;
            if (fileDlg.Show() == -1 && fileDlg.SelectedItems.Count > 0)   // -1 是点击了“选择文件夹”这个按钮；点“取消”会返回 0。
            {
                let pathArray = [];
                for (let i = 1; i <= fileDlg.SelectedItems.Count; i++) {
                    let filePath = fileDlg.SelectedItems.Item(i);
                    pathArray.push(getFileNameByPath(filePath));
                }

                // 找到的数据放在新表中
                var workbook = wps.EtApplication().ActiveWorkbook;
                var newSheet = workbook.Sheets.Add();
                newSheet.Cells.NumberFormatLocal = "@";   // 必须设置为文本格式

                for (let i = 1; i <= pathArray.length; i++) {
                    var cell = newSheet.Cells.Item(i, 1);
                    cell.Value2 = pathArray[i - 1];
                }

                newSheet.Columns.Item(1).AutoFit();
            }
            break;
        }
        case "btnExistFileNames": {
            // 检查指定目录下是否存在选择区域内的这些文件（例如选择了一些有人名的单元格，看有没有以这些人名命名的文件在这个文件夹下面）。
            let aSheet = wps.EtApplication().ActiveSheet;
            let selection = wps.Application.Selection;
            let sArray = selection.Value2;
            let realValuesCount = 0;
            for (let i = 0; i < sArray.length; i++) {
                var subArray = sArray[i];
                if (subArray == undefined || sArray.length <= 0)
                    continue;
                for (let i2 = 0; i2 < subArray.length; i2++) {
                    var s = subArray[i2];
                    if (s == undefined) continue;

                    var text = s[i2].toString();
                    if (text == undefined || text.length <= 0) continue;

                    realValuesCount++;
                }
            }

            if (realValuesCount <= 0) {
                alert("请选择一些有实际内容的单元格（例如一列姓名），本功能将检查指定的目录下有没有以这些单元内的文本命名的文件！");
                return
            }

            let fileDlg = Application.FileDialog(Application.Enum.msoFileDialogFilePicker);
            fileDlg.InitialFileName = Application.ActiveWorkbook.Path;
            fileDlg.AllowMultiSelect = true;
            if (fileDlg.Show() == -1 && fileDlg.SelectedItems.Count > 0)   // -1 是点击了“选择文件夹”这个按钮；点“取消”会返回 0。
            {
                let pathArray = [];
                for (let i = 1; i <= fileDlg.SelectedItems.Count; i++) {
                    let filePath = fileDlg.SelectedItems.Item(i);
                    pathArray.push(getFileNameByPath(filePath));
                }

                if (pathArray.length <= 0) {
                    alert("请按 Ctrl+A 选中指定目录下所有文件！");
                    return;
                }

                // 将两个数组比对
                wps.EtApplication.ScreenUpdating = false;  // 禁止屏幕刷新
                let foundCount = 0;
                for (let i = 0; i < sArray.length; i++) {
                    var subArray = sArray[i];
                    if (subArray == undefined || subArray.length <= 0)
                        continue;
                    for (let i2 = 0; i2 < subArray.length; i2++) {
                        if (subArray[i2] == null) continue;
                        let text = subArray[i2].toString().toLowerCase();
                        let found = false;
                        for (let j = 0; j < pathArray.length; j++) {
                            let path = pathArray[j];
                            if (path == undefined || path.length <= 0)
                                continue;
                            if (path.toLowerCase().indexOf(text) >= 0) {
                                found = true;
                                foundCount++;
                                break;
                            }
                        }

                        var cell = aSheet.Cells.Item(selection.Row + i, selection.Column + i2);
                        var erng = aSheet.Range(cell, cell);
                        if (found) {
                            subArray[i2] = subArray[i2] + " √";

                            erng.Interior.Pattern = -4142;
                            erng.Font.ColorIndex = -4105;  // xlColorIndexAutomatic 自动配色
                            erng.Font.TintAndShade = 0;
                        } else {
                            erng.Interior.Pattern = 1;
                            erng.Interior.Color = 65535;
                            erng.Interior.TintAndShade = 0;
                            erng.Font.Color = 255;
                            erng.Font.TintAndShade = 0;
                        }
                    }
                }
                wps.Application.Selection.Value2 = sArray;
                wps.EtApplication.ScreenUpdating = true;  // 恢复屏幕刷新
                alert("指定查找" + realValuesCount + "个条件，其中找到" + foundCount + "个文件，未找到 " + (realValuesCount - foundCount) + "个文件。")
            }
            break;
        }
        case "btnJoinRecordByField":
            {
                let joinpaneId = wps.PluginStorage.getItem("joinpane_id");
                if (!joinpaneId) {
                    let joinpane = wps.CreateTaskPane(GetUrlPath() + "/ui/joinpane.html", "合并右值");
                    let id = joinpane.ID;
                    wps.PluginStorage.setItem("joinpane_id", id);
                    joinpane.Visible = true;
                } else {
                    let joinpane = wps.GetTaskPane(joinpaneId);
                    joinpane.Visible = !joinpane.Visible;
                }
                break;
            }
        case "btnPostRecord":
            {
                // 尝试用 JS 发送数据
                // function SubmitAnswers(url, tpaperid, stunameval ,stugradeval, stuclassval, stunumval, stuanswers)
                SubmitAnswers("http://127.0.0.1:3001/answers", "2023020301", "张三", "2022", "01", 52, "ABCDEFGHIJKL");
                break;
            }
        case "btnSplitToSheetsByField":
            {
                var aSheet = wps.EtApplication().ActiveSheet;
                let s = wps.Application.Selection;
                if (s.Columns.Count != 1) {
                    alert("此功能只支持选中单列，且必须在调用前先对按当前选中列对当前工作表进行排序！");
                    return false;
                } else {
                    if (true != confirm("此功能只支持选中单列中某些数据单元格（不包括标题），这些单元格中也不能有特殊字符，还必须在调用前先对按当前选中列对当前工作表进行排序！" + 
                                        "\n注意：选中列中第一个单元格前的一行会被视为标题行！" +
                                        "\n\n您确定要继续吗？"))
                        return;
                }

                var titleArray = undefined;
                if(s.Row - aSheet.UsedRange.Row >= 1){
                    var titleRange = aSheet.Range(aSheet.Cells.Item(s.Row - 1, aSheet.UsedRange.Column), aSheet.Cells.Item(s.Row - 1, aSheet.UsedRange.Column + aSheet.UsedRange.Columns.Count - 1));
                    titleArray = titleRange.Value2[0];   // 要把二维数组转成一维
                }
                var usedArray = aSheet.UsedRange.Value2;
                var usedArrayRow = aSheet.UsedRange.Row;
                let preValue = undefined;
                var newWorkBook = wps.EtApplication().Workbooks.Add();
                var existNewSheet = undefined;
                if(newWorkBook.Sheets.Count == 1){
                    existNewSheet = newWorkBook.Sheets.Item(1);
                }
                var rowsArray = []             // 二维数组，用以赋给新表的Sheet
                var titleRowsCount = 0;
                var startRow = -1;
                for (i = 1; i <= s.Count; i++) {
                    let cell = s.Item(i);
                    if (cell == undefined) {
                        continue;
                    }

                    if (startRow < 0) { startRow = cell.Row; }

                    if (preValue == undefined) {
                        preValue = cell.Value2.toString();
                        if(titleArray != undefined && titleRowsCount <= 0){
                            rowsArray.push(titleArray);
                            titleRowsCount++;
                        }
                        rowsArray.push(usedArray[startRow - usedArrayRow + i - 1]);
                        continue;
                    }

                    var thisVal = cell.Value2.toString();
                    if (thisVal == preValue) {
                        if(titleArray != undefined && titleRowsCount <= 0){
                            rowsArray.push(titleArray);
                            titleRowsCount++;
                        }
                        rowsArray.push(usedArray[startRow - usedArrayRow + i - 1]);
                        continue;
                    } else {
                        // 与前一个值不同，说明 rowsArray 中的子数组列表应当放到一个新表中去。
                        var newWorksheet = undefined;
                        if(existNewSheet != undefined){
                            newWorksheet = existNewSheet;
                            existNewSheet = undefined;  // 必须回置
                        }else{
                            newWorksheet = newWorkBook.Sheets.Add(null, newWorkBook.Sheets.Item(newWorkBook.Sheets.Count));
                        }
                        newWorksheet.Name = preValue;
                        newWorksheet.Cells.NumberFormatLocal = "@";   // 必须设置为文本格式
                        var erng = newWorksheet.Range(newWorksheet.Cells.Item(1, 1), newWorksheet.Cells.Item(rowsArray.length, aSheet.UsedRange.Columns.Count)).Value2 = rowsArray;
                        preValue = thisVal;
                        rowsArray = [];
                        titleRowsCount = 0;
                        if(titleArray != undefined && titleRowsCount <= 0){
                            rowsArray.push(titleArray);
                            titleRowsCount++;
                        }
                        rowsArray.push(usedArray[startRow - usedArrayRow + i - 1]);
                        continue;
                    }

                }

                if (rowsArray.length > 0) {
                    var newWorksheet = newWorkBook.Sheets.Add(null, newWorkBook.Sheets.Item(newWorkBook.Sheets.Count));
                    newWorksheet.Name = preValue;
                    newWorksheet.Cells.NumberFormatLocal = "@";   // 必须设置为文本格式
                    var erng = newWorksheet.Range(newWorksheet.Cells.Item(1, 1), newWorksheet.Cells.Item(rowsArray.length, aSheet.UsedRange.Columns.Count)).Value2 = rowsArray;
                }
                break;
            }
        default:
            break;
    }
    return true
}


/*
* 已知文件路径，获取文件名xxx.doc
*/
function getFileNameByPath(path) {
    var index1 = path.lastIndexOf("\\"); // lastIndexOf("\\")  找到最后一个  /  的位置
    var fileName = path;
    if (index1 >= 0) {
        fileName = path.substr(index1 + 1); // substr() 截取剩余的字符，即得文件名xxx.doc
    } else {
        var index2 = path.lastIndexOf("/");
        if (index2 >= 0) {
            fileName = path.substr(index2 + 1);
        }
    }
    return fileName;
};

/*
* 已知文件路径，获取文件类型doc
*/
function getFileTypeByPath(path) {
    var index = path.lastIndexOf("."); // lastIndexOf(".")  找到最后一个  /  的位置
    var fileType = path.substr(index + 1); // substr() 截取剩余的字符，即文件名doc

    return fileType;
};

/*
* 已知文件路径，获取文件后缀.doc
*/
function getFileExtensionByPath(path) {
    var index1 = path.lastIndexOf("."); // lastIndexOf("/")  找到最后一个  /  的位置
    var index2 = path.length;
    var fileExtension = path.substr(index1, index2); // substr() 截取剩余的字符，即文件名.doc

    return fileExtension;
};


//银行卡号校验
//Description: 银行卡号Luhm校验
//Luhm校验规则：16位银行卡号（19位通用）:
// 1.将未带校验位的 15（或18）位卡号从右依次编号 1 到 15（18），位于奇数位号上的数字乘以 2。
// 2.将奇位乘积的个十位全部相加，再加上所有偶数位上的数字。
// 3.将加法和加上校验位能被 10 整除。
function luhmCheck(bankno) {
    if (bankno.length < 15 && bankno.length > 20) {
        //$("#banknoInfo").html("银行卡号长度必须在16到19之间");
        return false;
    }
    var num = /^\d*$/; //全数字
    if (!num.exec(bankno)) {
        //$("#banknoInfo").html("银行卡号必须全为数字");
        return false;
    }
    //开头6位
    var strBin = "10,18,30,35,37,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,58,60,62,65,68,69,84,87,88,94,95,98,99";
    if (strBin.indexOf(bankno.substring(0, 2)) == -1) {
        //$("#banknoInfo").html("银行卡号开头6位不符合规范");
        return false;
    }
    var lastNum = bankno.substr(bankno.length - 1, 1);//取出最后一位（与luhm进行比较）
    var first15Num = bankno.substr(0, bankno.length - 1);//前15或18位
    var newArr = new Array();
    for (var i = first15Num.length - 1; i > -1; i--) { //前15或18位倒序存进数组
        newArr.push(first15Num.substr(i, 1));
    }
    var arrJiShu = new Array(); //奇数位*2的积 <9
    var arrJiShu2 = new Array(); //奇数位*2的积 >9
    var arrOuShu = new Array(); //偶数位数组
    for (var j = 0; j < newArr.length; j++) {
        if ((j + 1) % 2 == 1) {//奇数位
            if (parseInt(newArr[j]) * 2 < 9) {
                arrJiShu.push(parseInt(newArr[j]) * 2);
            } else {
                arrJiShu2.push(parseInt(newArr[j]) * 2);
            }
        } else { //偶数位
            arrOuShu.push(newArr[j]);
        }
    }
    var jishu_child1 = new Array(); //奇数位*2 >9 的分割之后的数组个位数
    var jishu_child2 = new Array(); //奇数位*2 >9 的分割之后的数组十位数
    for (var h = 0; h < arrJiShu2.length; h++) {
        jishu_child1.push(parseInt(arrJiShu2[h]) % 10);
        jishu_child2.push(parseInt(arrJiShu2[h]) / 10);
    }
    var sumJiShu = 0; //奇数位*2 < 9 的数组之和
    var sumOuShu = 0; //偶数位数组之和
    var sumJiShuChild1 = 0; //奇数位*2 >9 的分割之后的数组个位数之和
    var sumJiShuChild2 = 0; //奇数位*2 >9 的分割之后的数组十位数之和
    var sumTotal = 0;
    for (var m = 0; m < arrJiShu.length; m++) {
        sumJiShu = sumJiShu + parseInt(arrJiShu[m]);
    }
    for (var n = 0; n < arrOuShu.length; n++) {
        sumOuShu = sumOuShu + parseInt(arrOuShu[n]);
    }
    for (var p = 0; p < jishu_child1.length; p++) {
        sumJiShuChild1 = sumJiShuChild1 + parseInt(jishu_child1[p]);
        sumJiShuChild2 = sumJiShuChild2 + parseInt(jishu_child2[p]);
    }
    //计算总和
    sumTotal = parseInt(sumJiShu) + parseInt(sumOuShu) + parseInt(sumJiShuChild1) + parseInt(sumJiShuChild2);
    //计算Luhm值
    var k = parseInt(sumTotal) % 10 == 0 ? 10 : parseInt(sumTotal) % 10;
    var luhm = 10 - k;
    if (lastNum == luhm) {
        //$("#banknoInfo").html("Luhm验证通过");
        return true;
    }
    else {
        //$("#banknoInfo").html("银行卡号必须符合Luhm校验");
        return false;
    }
}

function SubmitAnswers(url, tpaperid, stunameval ,stugradeval, stuclassval, stunumval, stuanswers)
{
    var stuid = stugradeval + stuclassval + stunumval;
    if(stunameval == ""){ alert("未提供姓名");return;}
    if(stugradeval == ""){ alert("未提供年级");return;}
    if(stuclassval == ""){ alert("未提供班级");return;}
    if(stunumval == ""){ alert("未提供学号");return;}
    var pattern =/[0-9]{1,2}$/ 
    if(pattern.test(stuclassval) == false){ alert("班级只能是 2 位以内数字！");return;}
    if(pattern.test(stunumval) == false){ alert("编号只能是 2 位以内数字！");return;}
    if(stunumval.length == 1){ stunumval = '0' + stunumval; }
    
    var time = new Date();
    var stu_comment = time.toLocaleString();
    var stu_data = {
        stu_id: stuid,
        stu_name: stunameval,
        stu_grade: stugradeval,
        stu_class: stuclassval,
        answers: stuanswers,
        test_paper_id: tpaperid,
        submit_time: stu_comment,
        comment: ''
    };
    $.ajax({
        type: "post",
        url: "http://127.0.0.1:3001/answers",
        data: stu_data,
        dataType: "json",
        crossDomain: true,
        success: function(data) {
            alert("已提交记录！");
        },
        error: function(){
            alert('提交出错！请稍后重试或联系管理员！');
        }
    });
}

function GetImage(control) {
    const eleId = control.Id
    switch (eleId) {
        case "btnShowMsg":
            return "images/1.svg"
        case "btnShowDialog":
            return "images/2.svg"
        case "btnShowTaskPane":
            return "images/3.svg"
        case "btnShowFindPane":
            return "images/find.svg"
        case "btnValidateBankNumber":
            return "images/bank.svg"
        case "btnValidateIdNumber":
            return "images/id.svg"
        case "btnSetBackgroundByField":
            return "images/bg.svg"
        case "btnSetBackgroundByFstchar":
            return "images/bg-fst-name.svg"
        case "btnMail":
            return "images/mail.svg"
        case "btnGetFileNames":
        case "btnExistFileNames":
            return "images/filenames.svg"
        case "btnJoinRecordByField":
            return "images/join-record.svg"
        case "btnPostRecord":
            return "images/post.svg"
        case "btnDropDataByFields":
            return "images/cells.svg"
        case "btnSetBackgroundByFstcharPinyin":
            return "images/first-pinyin.svg"
        case "btnConvertToPinYin":
            return "images/pinyin.svg"
        case "btnSplitToSheetsByField":
            return "images/spliter.svg"
        case "btnMultiReplace":
            return "images/replace.svg"
        case "btnHookData":
            return "images/hook.svg"
            default:
            ;
    }
    return "images/newFromTemp.svg"
}

function OnGetEnabled(control) {
    const eleId = control.Id
    switch (eleId) {
        case "btnShowMsg":
            return true
            break
        case "btnShowDialog":
            {
                let bFlag = wps.PluginStorage.getItem("EnableFlag")
                return bFlag
                break
            }
        case "btnShowTaskPane":
            {
                let bFlag = wps.PluginStorage.getItem("EnableFlag")
                return bFlag
            }
        case "btnShowFindPane":
            {
                // let bFlag = wps.PluginStorage.getItem("EnableFlag")
                // return bFlag
                // break
                return true;
            }
        case "btnValidateBankNumber":
            {
                return true;
            }
        case "btnValidateIdNumber":
            {
                return true;
            }
        case "btnSetBackgroundByField":
            {
                return true;
            }
        case "btnSetBackgroundByFstchar":
            {
                return true;
            }
        case "btnMail":
            {
                return true;
            }
        case "btnGetFileNames":
            {
                return true;
            }
        case "btnExistFileNames":
            {
                return true;
            }
        case "btnDropDataByFields":
            {
                return true;
            }
        case "btnSetBackgroundByFstcharPinyin":
            {
                return true;
            }
        case "btnConvertToPinYin":
            {
                return true;
            }
        case "btnSplitToSheetsByField":
            {
                return true;
            }
        case "btnMultiReplace":
            {
                return true;
            }
        case "btnHookData":
            {
                return true;
            }
        case "btnPostRecord":
            {
                return true;
            }
        default:
            break
    }
    return true
}

function OnGetVisible(control) {
    const eleId = control.Id;
    if (eleId === 'btnPostRecord'){
        return false;
    }
    return true;
    // switch (eleId) {
    //     case "btnTrimBlankChars": {
    //         return false;  // 本来考虑单元格里通常不会在首尾留空格，这个按钮留着碍眼。后来发现如果单元格里是文本，这个按钮还是有用的。
    //     } default: {
    //         return true;
    //     }
    // }
}

function OnGetLabel(control) {
    const eleId = control.Id
    switch (eleId) {
        case "btnIsEnbable":
            {
                let bFlag = wps.PluginStorage.getItem("EnableFlag")
                return bFlag ? "按钮Disable" : "按钮Enable"
                break
            }
        case "btnApiEvent":
            {
                let bFlag = wps.PluginStorage.getItem("ApiEventFlag")
                return bFlag ? "清除新建文件事件" : "注册新建文件事件"
                break
            }
    }
    return ""
}

function OnNewDocumentApiEvent(doc) {
    alert("新建文件事件响应，取文件名: " + doc.Name)
}

/**
 * 身份证号合法性验证 
 * 支持15位和18位身份证号
 * 支持地址编码、出生日期、校验位验证
 * 摘自：http://www.nowamagic.net/librarys/veda/detail/214
 * 最近需要对身份证合法性进行验证，实名验证是不指望了，不过原来的验证规则太过简单，只是简单的验证了身份证长度，现在业务需要加强下身份证验证规则，网上找到了不少资料，不过都不合偶的心意，无奈只好直接写一个，代码还是用自己的舒服哈。
 * 已实现功能：支持15位和18位身份证号，支持地址编码、出生日期、校验位验证。
 * 根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。
 *  地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。
 *  出生日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。
 *  顺序码表示同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。顺序码的奇数分给男性，偶数分给女性。
 *  校验码是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。
 * 出生日期计算方法：
 *  15位的身份证编码首先把出生年扩展为4位，简单的就是增加一个19或18,这样就包含了所有1800-1999年出生的人;
 *  2000年后出生的肯定都是18位的了没有这个烦恼，至于1800年前出生的,那啥那时应该还没身份证号这个东东。
 * 下面是正则表达式:
 *  出生日期1800-2099 (18|19|20)?\d{2}(0[1-9]|1[12])(0[1-9]|[12]\d|3[01])
 *  身份证正则表达式 /^\d{6}(18|19|20)?\d{2}(0[1-9]|1[12])(0[1-9]|[12]\d|3[01])\d{3}(\d|X)$/i
 *  15位校验规则 6位地址编码+6位出生日期+3位顺序号
 *  18位校验规则 6位地址编码+8位出生日期+3位顺序号+1位校验位
 * 校验位规则：公式:∑(ai×Wi)(mod 11)……………………………………(1)
 * 公式(1)中：
 *     i----表示号码字符从由至左包括校验码在内的位置序号；
 *     ai----表示第i位置上的号码字符值；
 *     Wi----示第i位置上的加权因子，其数值依据公式Wi=2^(n-1）(mod 11)计算得出。
 *     i 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1
 *     Wi 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 1
 * @param {String} code 
 * @returns 
 */
function IdentityCodeValid(code) {
    var city = { 11: "北京", 12: "天津", 13: "河北", 14: "山西", 15: "内蒙古", 21: "辽宁", 22: "吉林", 23: "黑龙江 ", 31: "上海", 32: "江苏", 33: "浙江", 34: "安徽", 35: "福建", 36: "江西", 37: "山东", 41: "河南", 42: "湖北 ", 43: "湖南", 44: "广东", 45: "广西", 46: "海南", 50: "重庆", 51: "四川", 52: "贵州", 53: "云南", 54: "西藏 ", 61: "陕西", 62: "甘肃", 63: "青海", 64: "宁夏", 65: "新疆", 71: "台湾", 81: "香港", 82: "澳门", 91: "国外 " };
    var tip = "";
    var pass = true;

    if (!code || !/^\d{6}(18|19|20)?\d{2}(0[1-9]|1[012])(0[1-9]|[12]\d|3[01])\d{3}(\d|X)$/i.test(code)) {
        tip = "身份证号格式错误";
        pass = false;
    }

    else if (!city[code.substr(0, 2)]) {
        tip = "地址编码错误";
        pass = false;
    }
    else {
        //18位身份证需要验证最后一位校验位
        if (code.length == 18) {
            code = code.split('');
            //∑(ai×Wi)(mod 11)
            //加权因子
            var factor = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2];
            //校验位
            var parity = [1, 0, 'X', 9, 8, 7, 6, 5, 4, 3, 2];
            var sum = 0;
            var ai = 0;
            var wi = 0;
            for (var i = 0; i < 17; i++) {
                ai = code[i];
                wi = factor[i];
                sum += ai * wi;
            }
            var last = parity[sum % 11];
            if (parity[sum % 11] != code[17]) {
                tip = "校验位错误";
                pass = false;
            }
        }
    }
    // if(!pass) alert(tip);  // 这要提示很多次，通常我用这个功能是批量校验！
    return pass;
}